/**
 * @author Eduardo Leonne Souza de Oliveira
 * @version 1.0
 * Date: 2011-12-29
 * Last change: 2011-12-29 
 * 
 */

package com.leonne.harpacristabrasileira.dao;

import java.io.File;
import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;

import com.leonne.harpacristabrasileira.lists.vo.AnthemVO;
import com.leonne.harpacristabrasileira.tools.MyConstants;
import com.leonne.harpacristabrasileira.tools.Utils;

public class AnthemsDAO extends GenericDAO {

	/**
	 * DAO created specifically to guarantee that the database structure will be
	 * created and populated
	 * 
	 * @param context
	 *            Application base context
	 */
	public AnthemsDAO(Context context) {
		super(context, "anthems");
	}

	/**
	 * Get records in the anthem table
	 * 
	 * @return ArrayList A list of AnthemVO objects
	 */
	public ArrayList<AnthemVO> fetch(String search) {
		ArrayList<AnthemVO> anthems = new ArrayList<AnthemVO>();
		File f = new File("/data/data/com.android.leonne.harpacristabrasileira/files/Favorites.temp");
		if (f.exists()){
			String str = Utils.openFile("Favorites.temp", this.getContext());
			if (!str.equals("")){
				String sql = "UPDATE ANTHEMS SET isfavorite = 1 WHERE idanthem IN ("+ str +")";
				this.execSql(sql);
			}
			f.delete();
		}
		String sql = "SELECT an.idanthem, an.title, ve.verse, an.isfavorite "
				+ "FROM anthems an "
				+ "LEFT JOIN verses ve ON an.idanthem = ve.idanthem "
				+ "WHERE (ve.ismainverse = 1 OR (ve.ismainverse = 0 and ve.verseorder = 1)) ";
		if (!search.equals("")) {
			if (Utils.isNumeric(search))
				sql += "AND an.idanthem = " + search;
			else
				sql += "AND (an.title LIKE '%" + search + "%'"
					  + " OR ve.verse LIKE '%" + search + "%'" + ")";
		}
		if (MyConstants.ISFAVORITE)
			sql += "AND an.isfavorite=1";
		
		sql += " GROUP BY an.idanthem";
			
		Cursor cursor = this.getDatabase().rawQuery(sql, null);
		if (cursor.moveToFirst()) {
			do {
				AnthemVO an = new AnthemVO();
				an.setIdAnthem(cursor.getInt(0));
				an.setTitle(cursor.getString(1));
				an.setMainVerse(cursor.getString(2));
				an.setIsFavorite(cursor.getInt(3));
				anthems.add(an);
			} while (cursor.moveToNext());
		}
		return anthems;
	}

}
